package org.kjtc.util;

import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;

public class ExcelUtil {

	public static List<?> importExcel(InputStream in, String jsonFilePath, Class<?> cls) throws Exception {
		List<Object> list = new ArrayList<Object>();
		JSONArray jsonArray = Tools.readJsonFile(jsonFilePath, false);
		if (jsonArray != null) {
			HSSFWorkbook workbook = new HSSFWorkbook(in);
			int sheetCount = workbook.getNumberOfSheets();
			int length = jsonArray.size();
			HSSFSheet sheet = null;
			HSSFRow row = null;
			HSSFCell cell = null;
			Object setObject = null;
			int rowCount = 0;
			int columnCount = 0;
			List<String> headerNameENList = null;
			String cellHeaderName = "";
			String headerNameCN = "";
			String headerNameEN = "";
			Class<?> returnType = null;
			Method getMethod = null;
			Method setMethod = null;
			JSONObject json = null;
			for (int i = 0; i < sheetCount; i++) {
				sheet = workbook.getSheetAt(i);
				row = sheet.getRow(0);
				columnCount = row.getLastCellNum();
				headerNameENList = new ArrayList<String>();
				for (int l = 0; l < columnCount; l++) {
					cell = row.getCell(l);
					cellHeaderName = cell.getStringCellValue();
					for (int m = 0; m < length; m++) {
						json = jsonArray.getJSONObject(m);
						if (json != null && json.has("headerNameCN")) {
							headerNameCN = json.getString("headerNameCN");
							if (cellHeaderName.equals(headerNameCN)) {
								if (json.has("headerNameEN")) {
									headerNameEN = json.getString("headerNameEN");
								}
							}
							headerNameCN = "";
						}
						json = null;
					}
					headerNameENList.add(headerNameEN);
					headerNameEN = "";
					cellHeaderName = "";
				}
				rowCount = sheet.getPhysicalNumberOfRows();
				for (int j = 1; j < rowCount; j++) {
					row = sheet.getRow(j);
					if (row == null || row.getCell(0) == null || getCellValue(row.getCell(0), row.getCell(0).getCellType()).isEmpty()) {
						break;
					}
					for (int k = 0; k < columnCount; k++) {
						cell = row.getCell(k);
						if (cell == null) {
							continue;
						}
						headerNameEN = headerNameENList.get(k);
						if (headerNameEN != null && !"".equals(headerNameEN)) {
							getMethod = cls.getMethod("get" + headerNameEN);
							returnType = getMethod.getReturnType();
							setMethod = cls.getDeclaredMethod("set" + headerNameEN, returnType);
							if (setObject == null) {
								setObject = cls.newInstance();
							}
							setMethod.invoke(setObject, getMethodValue(returnType.getSimpleName().toLowerCase(), getCellValue(cell, cell.getCellType())));
							getMethod = null;
							returnType = null;
							setMethod = null;
						}
						headerNameEN = "";
					}
					if (setObject != null) {
						list.add(setObject);
					}
					setObject = null;
				}
				headerNameENList = null;
			}
		} else {
			list = null;
		}
		return list;
	}

	private static String getCellValue(HSSFCell cell, int cellType){
		String cellValue = null;
		switch(cellType) {
			case HSSFCell.CELL_TYPE_NUMERIC://数字
				if (DateUtil.isCellDateFormatted(cell)){
					SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					cellValue = dateFormat.format(cell.getDateCellValue());
				} else {
					DecimalFormat df = new DecimalFormat("0");
					cellValue = df.format(cell.getNumericCellValue());
				}
				break;
			case HSSFCell.CELL_TYPE_STRING://字符串
				cellValue = cell.getStringCellValue();
				break;
			case HSSFCell.CELL_TYPE_FORMULA://公式
				cellValue = cell.getCellFormula();
				break;
			case HSSFCell.CELL_TYPE_BLANK://空白
				cellValue = null;
				break;
			case Cell.CELL_TYPE_BOOLEAN://布尔
				cellValue = String.valueOf(cell.getBooleanCellValue());
				break;
			case HSSFCell.CELL_TYPE_ERROR://异常
				cellValue = null;
				break;
			default:
				break;
		}
		return cellValue;
	}

	private static Object getMethodValue(String methodType, String cellValue){
		Object methodValue = null;
		try {
			if (cellValue == null || "".equals(cellValue)) {
				methodValue = cellValue;
			} else if ("long".equals(methodType)) {
				methodValue = Long.parseLong(cellValue);
			} else if ("integer".equals(methodType) || "int".equals(methodType)) {
				methodValue = Integer.parseInt(cellValue);
			} else if ("short".equals(methodType)) {
				methodValue = Short.parseShort(cellValue);
			} else if ("double".equals(methodType)) {
				methodValue = Double.parseDouble(cellValue);
			} else if ("float".equals(methodType)) {
				methodValue = Float.parseFloat(cellValue);
			} else if ("date".equals(methodType)) {
				methodValue = formatDate(cellValue);
			} else if ("boolean".equals(methodType)) {
				if ("是".equals(cellValue)) {
					methodValue = true;
				} else {
					methodValue = false;
				}
			} else {
				methodValue = cellValue;
			}
		} catch (NumberFormatException e) {
			return null;
		}
		return methodValue;
	}

	private static Date formatDate(String date){
		String dateFormate = "yyyy-MM-dd HH:mm:ss";
		try {
			if(date.length() == 10) {
				dateFormate = "yyyy-MM-dd";
			} else if(date.length() == 13) {
				dateFormate = "yyyy-MM-dd HH";
			} else if (date.length() == 16) {
				dateFormate = "yyyy-MM-dd HH:mm";
			}
			SimpleDateFormat sdf = new SimpleDateFormat(dateFormate);
			return sdf.parse(date);
		} catch (ParseException e) {
			return null;
		}
	}
	/**
	 * @param title   表格标题名
	 * @param jsonFilePath json文件路径，导出excel文件的格式
	 * @param dataList 需要显示的数据集合,此方法支持的javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
	 * @param response http请求相应
	 * @throws Exception
	 */
	public static void exportExcel(String excelFilename, JSONArray jsonArray, Map<String, Object> map, HttpServletResponse response) throws Exception {
		response.setCharacterEncoding("UTF-8");
		response.setContentType("application/x-download");
		response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelFilename + Const.EXCEL_SUFFIX, "UTF-8"));
		if (excelFilename == null && "".equals(excelFilename)) {
			excelFilename = "ExcelFile";
		}
		if (jsonArray != null) {
			// 声明工作薄
			HSSFWorkbook workbook = new HSSFWorkbook();
			int length = jsonArray.size();
			JSONObject json = null;
			String dataListName = null;
			Collection<?> dataList = null;
			for (int i = 0; i < length; i++) {
				json = jsonArray.getJSONObject(i);
				if (json != null && json.has("dataListName")) {
					if (json.has("printFlag") && "1".equals(json.getString("printFlag"))) {
						dataListName = json.getString("dataListName");
						dataList = (Collection<?>)map.get(dataListName);
						createSheet(workbook, json, dataList);
					}
				}
				json = null;
				dataListName = null;
				dataList = null;
			}
			OutputStream out = response.getOutputStream();
			workbook.write(out);
			out.close();
			workbook = null;
		}
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	private static void createSheet(HSSFWorkbook workbook, JSONObject json, Collection<?> dataList) throws Exception{
		// 生成样式
		HSSFCellStyle style = createCellStyle(workbook, (short)0, (short)0);
		// 设置表格背景颜色为黄色
		HSSFCellStyle styleYellow = null;
		// 设置表格字体粗体
		HSSFCellStyle styleHeader = createCellStyle(workbook, HSSFColor.SKY_BLUE.index, HSSFFont.BOLDWEIGHT_BOLD);
		// 设置表格的数据格式
		HSSFCellStyle styleDataFormat = null;
		CreationHelper createHelper = null;
		String excelSheetName = "sheet";
		if (json != null && json.has("sheetName")) {
			excelSheetName = json.getString("sheetName");
		}
		// 生成表格
		HSSFSheet sheet = workbook.createSheet(excelSheetName);
		// 打印设定
		HSSFPrintSetup ps = sheet.getPrintSetup();
		ps.setLandscape(true); // 打印方向，true：横向，false：纵向(默认)
		ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 纸张类型4A

		List<String> fieldList = new ArrayList<String>();
		JSONArray headerContent = null;
		HSSFCell cell = null;
		HSSFRow sheetRow = null;
		JSONObject headerJson = null;
		if (json != null && json.has("headerContent")) {
			// 设置表格标题
			sheetRow = sheet.createRow(0);
			headerContent = json.getJSONArray("headerContent");
			int headerLength = headerContent.size();
			String headerName = null;
			for (int i = 0; i < headerLength; i++) {
				int columnWidth = 5000;
				headerJson = headerContent.getJSONObject(i);
				headerName = "column" + (i + 1);
				if (headerJson != null){
					if (headerJson.has("headerNameCN")) {
						headerName = headerJson.getString("headerNameCN");
					}
					if (headerJson.has("headerNameEN")) {
						fieldList.add(headerJson.getString("headerNameEN"));
					}
					if (headerJson.has("headerWidth")) {
						columnWidth = Integer.parseInt(headerJson.getString("headerWidth"), 10);
					}
					// 表格每列的宽度
					sheet.setColumnWidth(i, columnWidth);
					// 表格样式
					cell = sheetRow.createCell(i);
					cell.setCellStyle(styleHeader);
					// 表格标题
					cell.setCellValue(headerName);
				}
				headerJson = null;
				headerName = null;
			}
			headerContent = null;
			sheetRow = null;
		}
		int fieldLength = fieldList.size();
		if (dataList != null && fieldLength > 0) {
			// 遍历集合数据，产生数据行
			Iterator<?> it = dataList.iterator();
			int index = 1;
			Object object = null;
			Object methodValue = null;
			Class cls = null;
			Method method = null;
			String strValue = null;
			String dateFormat = null;
			Date date = null;
			while (it.hasNext()) {
				// 表格行
				sheetRow = sheet.createRow(index);
				int column = 0;
				// 序号列
				cell = sheetRow.createCell(column);
				cell.setCellStyle(style);
				cell.setCellValue(index);
				// list中每一条数据对象
				object = it.next();
				// 每条数据对象包含的内容
				cls = object.getClass();
				for (int j = 0; j < fieldLength; j++) {
					String fiedName = fieldList.get(j);
					if (fiedName != null && !"".equals(fiedName)) {
						column = column + 1;
						cell = sheetRow.createCell(column);
						cell.setCellStyle(style);
						method = cls.getMethod("get" + fieldList.get(j));
						methodValue = method.invoke(object);
						if (methodValue instanceof Boolean) {
							boolean bValue = (Boolean) methodValue;
							if ("Enabled".equals(fieldList.get(j))) {
								strValue = bValue ? "是" : "否";
								cell.setCellValue(strValue);
								strValue = null;
							}
						} else if (methodValue instanceof Date) {
							dateFormat = "yyyy-MM-dd HH:mm:ss";
							date = (Date) methodValue;
							if (headerContent != null) {
								headerJson = headerContent.getJSONObject(j);
								if (headerJson != null && headerJson.has("dateFormat")) {
									if (!"".equals(headerJson.getString("dateFormat"))) {
										dateFormat = headerJson.getString("dateFormat");
									}
								}
								headerJson = null;
							}
							cell.setCellValue(date);
							if (cell.getNumericCellValue() < 0) {
								if (styleYellow == null) {
									styleYellow = createCellStyle(workbook, HSSFColor.YELLOW.index, (short)0);
								}
								cell.setCellStyle(styleYellow);
								SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
								cell.setCellValue(formatter.format(date));
							} else {
								if (createHelper == null) {
									createHelper = workbook.getCreationHelper();
								}
								if (styleDataFormat == null) {
									styleDataFormat = createCellStyle(workbook, (short)0, (short)0);
								}
								styleDataFormat.setDataFormat(createHelper.createDataFormat().getFormat(dateFormat));
								cell.setCellStyle(styleDataFormat);
							}
							dateFormat = null;
							date = null;

						} else if (methodValue instanceof byte[]) {
							HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
							// 有图片时，设置行高为60px;
							sheetRow.setHeightInPoints(60);
							// 设置图片所在列宽度为80px,注意这里单位的一个换算
							sheet.setColumnWidth(j, (short) (35.7 * 80));
							// sheet.autoSizeColumn(i);
							byte[] bsValue = (byte[]) methodValue;
							HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short)6, index, (short)6, index);
							anchor.setAnchorType(2);
							patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_PNG));
							anchor = null;
						} else if (methodValue instanceof Double) {
							cell.setCellValue((Double) methodValue);
						} else if (methodValue == null || "".equals(methodValue)) {
							if (styleYellow == null) {
								styleYellow = createCellStyle(workbook, HSSFColor.YELLOW.index, (short)0);
							}
							cell.setCellStyle(styleYellow);
							cell.setCellValue("");
						} else {
							cell.setCellValue(methodValue.toString());
						}
						methodValue = null;
						method = null;
					}
				}
				index++;
				cls = null;
				object = null;
				sheetRow = null;
			}
		}
	}

	private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short color, short font){
		// 生成样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置边框样式
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		if (color > 0){
			// 设置边框样式
			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style.setFillForegroundColor(color);
		}
		if (font > 0) {
			HSSFFont fontStyle = workbook.createFont();
			fontStyle.setBoldweight(font);
			style.setFont(fontStyle);
		}
		return style;
	}
}
